﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using MySql.Data.MySqlClient;
namespace Common.DBUtility
{
    public abstract class MysqlHelpers
    {
        #region 单表分页SQL语句生成
        /// <summary>
        /// 带格式的分页SQL语句，
        /// 格式内容：
        /// 0要选择的列，
        /// 1表名，
        /// 2唯一列名（一般为主键），
        /// 3排序字段及方式，
        /// 4选择条件，
        /// 5起始行号，
        /// 6结束行号，
        /// 7跟2对应的条件（如2为'clumA,clumB',则7应该为'a.clumA=b.clumA and a.clumB=b.clumB'）
        /// </summary>
        protected const string SQL_PAGE_MSSQL2005 = @"SELECT {0} FROM 
              (SELECT row_number_temp,{2} FROM 
                  (SELECT {2} FROM {1} {4})
                  AS t WHERE {3} LIMIT {5},{6})
               AS b INNER JOIN {1} AS a ON {7}";

        /// <summary>
        /// 带格式的分页SQL语句，
        /// 格式内容：
        /// 0要选择的列，
        /// 1表名，
        /// 
        /// 3排序字段及方式，
        /// 4选择条件，
        /// 5起始行号，
        /// 6结束行号
        /// </summary>
        protected const string SQL_PAGE_MSSQL2005_JION = @"SELECT * FROM 
                    (SELECT {0} FROM {1} {4})
                T WHERE {3} LIMIT {5},{6}";

        /// <summary>
        /// 带格式的分页SQL语句（第一页）
        /// 格式内容：0分页大小；1要选择的列；2表名；3选择条件(带where)；4排序方式(带order by)
        /// </summary>
        protected const string SQL_PAGE_1 = "select top {0} {1} from {2} {3} {4}";
        /// <summary>
        /// 带格式的分页SQL语句（第二页以上适用）
        /// 格式内容：0分页大小；1要选择的列；2表名；3唯一列名（一般为主键）；4起始索引；5选择条件(带where)；6排序方式(带order by)；7选择条件(带and)
        /// </summary>
        protected const string SQL_PAGE_2 = "select top {0} {1} from {2} where {3} not in (select top {4} {3} from {2} {5} {6}) {7} {6}";  //0分页大小，1要选择的字段，页码

        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// 注意：仅SqlServer2005以上可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL_MSSQL2005(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex)
        {
            return GetPageSQL_MSSQL2005(dbTableName, IdColumName, conditionSql, selectFields, orderBy, pageSize, pageIndex, 0);
        }

        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// 注意：仅SqlServer2005以上可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL_MSSQL2005(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset)
        {
            if (pageSize <= 0) pageSize = 10;
            if (pageIndex <= 0) pageIndex = 1;

            //if (pageRecordOffset <= 0) pageRecordOffset = 0;  
            int start = pageSize * (pageIndex - 1) - pageRecordOffset;
            if (start < 0)
            {
                pageSize += start;
                start = 0;
                if (pageSize < 0) pageSize = 0;
            }


            //如果是第一页,则直接使用top语句
            //if (pageIndex == 1) return GetPageSQL(dbTableName, IdColumName, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);

            if (selectFields.Trim() == "") selectFields = "*";
            if (orderBy.Trim() == "") orderBy = IdColumName;
            if (conditionSql.Trim() != "") conditionSql = "where " + conditionSql;


            StringBuilder sqlSb = new StringBuilder();
            sqlSb.AppendFormat(SQL_PAGE_MSSQL2005_JION, selectFields, dbTableName, IdColumName, orderBy, conditionSql, start + 1, start + pageSize);
            return sqlSb.ToString();
        }

        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// 注意：仅SqlServer2005以上可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL_MSSQL2005_JOIN(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex)
        {
            if (pageSize <= 0) pageSize = 10;
            if (pageIndex <= 0) pageIndex = 1;
            //如果是第一页,则直接使用top语句
            //if (pageIndex == 1) return GetPageSQL(dbTableName, IdColumName, conditionSql, selectFields, orderBy, pageSize, pageIndex);

            if (selectFields.Trim() == "") selectFields = "*";
            if (orderBy.Trim() == "") orderBy = IdColumName;
            if (conditionSql.Trim() != "") conditionSql = "where " + conditionSql;

            StringBuilder sqlSb = new StringBuilder();
            sqlSb.AppendFormat(SQL_PAGE_MSSQL2005_JION, selectFields, dbTableName, IdColumName, orderBy, conditionSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
            return sqlSb.ToString();
        }


        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// 注意：仅SqlServer2005以上可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出,小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL_MSSQL2005_JOIN(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset)
        {
            if (pageSize <= 0) pageSize = 10;
            if (pageIndex <= 0) pageIndex = 1;

            //if (pageRecordOffset <= 0) pageRecordOffset = 0;
            int start = pageSize * (pageIndex - 1) - pageRecordOffset;
            if (start < 0)
            {
                pageSize += start;
                start = 0;
                if (pageSize < 0) pageSize = 0;
            }

            //如果是第一页,则直接使用top语句
            //if (pageIndex == 1) return GetPageSQL(dbTableName, IdColumName, conditionSql, selectFields, orderBy, pageSize, pageIndex);

            if (selectFields.Trim() == "") selectFields = "*";
            if (orderBy.Trim() == "") orderBy = IdColumName;
            if (conditionSql.Trim() != "") conditionSql = "where " + conditionSql;

            StringBuilder sqlSb = new StringBuilder();
            sqlSb.AppendFormat(SQL_PAGE_MSSQL2005_JION, selectFields, dbTableName, IdColumName, orderBy, conditionSql, start + 1, start + pageSize);
            return sqlSb.ToString();
        }


        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// SqlServer2000/SqlServer2005可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex)
        {
            if (pageSize <= 0) pageSize = 10;
            if (pageIndex <= 0) pageIndex = 1;
            if (selectFields.Trim() == "") selectFields = "*";
            StringBuilder sqlSb = new StringBuilder();
            if (pageIndex > 1) sqlSb.AppendFormat(SQL_PAGE_2, pageSize, selectFields, dbTableName, IdColumName, pageSize * (pageIndex - 1), conditionSql.Trim() == "" ? "" : (" where " + conditionSql), orderBy.Trim() == "" ? "" : (" order by " + orderBy), conditionSql.Trim() == "" ? "" : (" and " + conditionSql));
            else sqlSb.AppendFormat(SQL_PAGE_1, pageSize, selectFields, dbTableName, conditionSql.Trim() == "" ? "" : (" where " + conditionSql), orderBy.Trim() == "" ? "" : (" order by " + orderBy));
            return sqlSb.ToString();
        }

        /// <summary>
        /// 返回按分页大小返回指定页码的指定字段的SQL语句
        /// SqlServer2000/SqlServer2005可用
        /// </summary>
        /// <param name="dbTableName">数据表名称</param>
        /// <param name="IdColumName">唯一列名（一般为主键）</param>
        /// <param name="conditionSql">指定条件(不包含where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式，不包含order by</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <returns>返回按分页大小返回指定页码的指定字段的SQL语句</returns>
        public static string GetPageSQL(string dbTableName, string IdColumName, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset)
        {
            if (pageSize <= 0) pageSize = 10;
            if (pageIndex <= 0) pageIndex = 1;
            //if (pageRecordOffset <= 0) pageRecordOffset = 0;
            int start = pageSize * (pageIndex - 1) - pageRecordOffset;
            if (start < 0)
            {
                pageSize += start;
                start = 0;
                if (pageSize < 0) pageSize = 0;
            }

            if (selectFields.Trim() == "") selectFields = "*";
            StringBuilder sqlSb = new StringBuilder();

            if (pageIndex > 1)
            {
                sqlSb.AppendFormat(SQL_PAGE_2, pageSize, selectFields, dbTableName, IdColumName, start, conditionSql.Trim() == "" ? "" : (" where " + conditionSql), orderBy.Trim() == "" ? "" : (" order by " + orderBy), conditionSql.Trim() == "" ? "" : (" and " + conditionSql));
            }
            else sqlSb.AppendFormat(SQL_PAGE_1, pageSize, selectFields, dbTableName, conditionSql.Trim() == "" ? "" : (" where " + conditionSql), orderBy.Trim() == "" ? "" : (" order by " + orderBy));
            return sqlSb.ToString();
        }
        #endregion

        // Hashtable to store cached parameters
        protected static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Execute a MySqlCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接语句</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>受影响的记录数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a MySqlCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, MySqlCommand cmd)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// Execute a MySqlCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a MySqlCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接语句</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a MySqlCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接语句</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a MySqlCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接语句</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a MySqlCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">数据库连接</param>
        /// <param name="cmdType">指定如何解释命令字符串(procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">参数组</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            MySqlCommand cmd = new MySqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd);
                DataSet myDataSet = new DataSet();
                myAdapter.Fill(myDataSet);
                cmd.Parameters.Clear();
                return myDataSet;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet GetDataSetWithTransaction(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd);
            DataSet myDataSet = new DataSet();
            myAdapter.Fill(myDataSet);
            cmd.Parameters.Clear();
            return myDataSet;
        }


        /// <summary>
        /// 将参数组加入缓存
        /// </summary>
        /// <param name="cacheKey">参数缓存的Key</param>
        /// <param name="cmdParms">需要缓存的参数组</param>
        public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 获取已经缓存的参数组
        /// </summary>
        /// <param name="cacheKey">用于查找参数的key</param>
        /// <returns>缓存的参数组，没有找到则返回NULL</returns>
        public static MySqlParameter[] GetCachedParameters(string cacheKey)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">MySqlCommand object</param>
        /// <param name="conn">MySqlConnection object</param>
        /// <param name="trans">MySqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        protected static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;
            cmd.CommandTimeout = 120;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        #region 由Object取值
        /// <summary>
        /// 取得Int16值
        /// </summary>
        public static Int16? GetInt16(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                short result;
                if (Int16.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得UInt16值
        /// </summary>
        public static UInt16? GetUInt16(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                ushort result;
                if (UInt16.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得Int值
        /// </summary>
        public static int? GetInt(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                int result;
                if (int.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得UInt值
        /// </summary>
        public static uint? GetUInt(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                uint result;
                if (uint.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得UInt64值
        /// </summary>
        public static ulong? GetULong(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                ulong result;
                if (ulong.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得byte值
        /// </summary>
        public static byte? GetByte(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                byte result;
                if (byte.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得sbyte值
        /// </summary>
        public static sbyte? GetSByte(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                sbyte result;
                if (sbyte.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得Long值
        /// </summary>
        public static long? GetLong(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                long result;
                if (long.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得Decimal值
        /// </summary>
        public static decimal? GetDecimal(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                decimal result;
                if (decimal.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得float值
        /// </summary>
        public static float? GetFloat(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                float result;
                if (float.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null; ;
            }
        }

        /// <summary>
        /// 取得double值
        /// </summary>
        public static double? GetDouble(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                double result;
                if (double.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得Guid值
        /// </summary>
        public static Guid? GetGuid(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                try
                {
                    Guid result = new Guid(obj.ToString());
                    return result;
                }
                catch
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得DateTime值
        /// </summary>
        public static DateTime? GetDateTime(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                DateTime result;
                if (DateTime.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得bool值
        /// </summary>
        public static bool? GetBool(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                bool result;
                obj = obj.ToString() == "1" ? "True" : "False";
                if (bool.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得byte[]
        /// </summary>
        public static byte[] GetBinary(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                return (byte[])obj;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 取得string值
        /// </summary>
        public static string GetString(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                return obj.ToString();
            }
            else
            {
                return null;
            }
        }
        #endregion

    }
}
